﻿-- select * from employees  查询所有员工

--查询特定的列
-- SELECT department_id, location_id
-- FROM 
-- departments;

-- 链接符
-- SELECT last_name||job_id AS "Employees"
-- FROM 
-- employees;

-- select last_name || ' is a ' || job_id "Employee"
-- from  employees

--去重
-- SELECT DISTINCT department_id
-- FROM 
-- employees

--操作优先级
-- SELECT last_name, salary, 12*salary+100
-- FROM 
-- employees;

--使用括号
-- SELECT last_name, salary, 12*(salary+100)
-- FROM 
-- employees

--使用数学运算符
-- SELECT last_name, salary, salary + 300
-- FROM 
-- employees;

--定义空值
-- SELECT last_name, job_id, salary, commission_pct
-- FROM 
-- employees;

--空值的数学运算
-- SELECT last_name, 12*salary*commission_pct
-- FROM 
-- employees;

--比较运算
-- SELECT last_name, salary
-- FROM 
-- employees
-- WHERE  salary <= 3000;

--between
-- SELECT last_name, salary
-- FROM 
-- employees
-- WHERE  salary BETWEEN 2500 AND 3500;

--in
--SELECT employee_id, last_name, salary, manager_id
--FROM 
--employees
--WHERE  manager_id IN (100, 101, 201);

--like
-- SELECT last_name
-- FROM 
-- employees
-- WHERE  last_name LIKE '_o%'

--使用NULL判断空值
-- SELECT last_name, manager_id
-- FROM 
-- employees
-- WHERE  manager_id IS NULL;

--使用别名
-- SELECT last_name AS name, commission_pct comm
-- FROM 
-- employees;

--去除重复行
-- SELECT DISTINCT department_id
-- FROM 
-- employees;

--显示表结构
--DESCRIBE employees

-- where条件过滤
-- SELECT *|{[DISTINCT] column|expression [alias],...}
-- FROM
-- table
--[WHERE condition(s)];
-- where子句
-- SELECT employee_id, last_name, job_id, department_id
-- FROM   
-- employees
-- WHERE  department_id = 90 ;

/*
   小写函数
   SELECT employee_id, last_name, department_id
   FROM 
   employees
   WHERE  LOWER(last_name) = 'higgins'
*/

--大写函数
-- SELECT employee_id, last_name, department_id
-- FROM 
-- employees
-- WHERE  UPPER(last_name) = UPPER('higgins')

--字符串和日期
-- SELECT last_name, job_id, department_id
-- FROM 
-- employees
-- WHERE  last_name = 'Whalen';

--字符串拼接
-- select concat('hello','world') from dual

--从字符串的指定位置开始，截取指定长度的子串。
-- select substr('helloworld',1,5) from dual

--求字符串长度
-- select length('hellow') from dual

--INSTR查找首次出现位置
-- select instr('helloworld','h') from dual

--lpad将原字符串左侧填充指定字符，使最终字符串达到指定长度。
-- SELECT LPAD('12356',10,'*') from dual

--rpad将原字符串右侧填充指定字符，使最终字符串达到指定长度。
-- SELECT RPAD('12356',10,'*') from dual

--去除字符串两端的空格
--select trim(' hello world ') from dual

--相同点：都是显示当前系统时间
--不同点：current_date返回的是当前会话时间，而sysdate返回的是服务器时间
-- select current_date,sysdate from dual

--查询每个人入职月份第一天
-- select last_day(hire_date-30)+1 from employees

--将数字转成字符串
-- select to_char(salary,'L99,999.00')
-- from employees

--NVL 函数 将空值转换成一个已知的值
-- select last_name, salary, NVL(commission_pct, 0),
-- (salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL
-- from employees

--使用NVL2函数  判断参数是否为空如果为空则选取后一个参数
-- SELECT last_name,  salary, commission_pct,
-- NVL2(commission_pct, 
-- 'SAL+COMM', 'SAL') income
-- FROM   employees WHERE department_id IN (50, 80)

--Nullif判断两个表达式是否相等，相等则返回 NULL，否则返回第一个表达式。

-- SELECT first_name, LENGTH(first_name) "expr1", last_name,  LENGTH(last_name)  "expr2",NULLIF(LENGTH(first_name), LENGTH(last_name)) result
-- FROM   employees;

-- COALESCE 函数 COALESCE 与NVL相比的优点在于COALESCE可以同时处理交替的多个值
-- SELECT   last_name,
-- COALESCE(commission_pct, salary, 10) comm
-- FROM     employees
-- ORDER BY commission_pct;

--CASE 表达

-- SELECT last_name, job_id, salary,
-- CASE job_id WHEN 'IT_PROG'  THEN  1.10*salary
-- WHEN 'ST_CLERK' THEN  1.15*salary
-- WHEN 'SA_REP'   THEN  1.20*salary
-- ELSE      salary END     "REVISED_SALARY"
-- FROM   employees;

--自连接
-- SELECT worker.last_name || ' works for ' 
-- || manager.last_name
-- FROM 
-- employees worker inner join employees manager on worker.manager_id = manager.employee_id 

--使用ON子句创建多表连接
/*
SELECT employee_id, city, department_name
  FROM employees e
  JOIN ON JOIN ON departments d locations l
 d.department_id = e.department_id
 d.location_id = l.location_id;
*/

--左外链接
/*

SELECT e.last_name, e.department_id, d.department_name
  FROM employees e
  LEFT OUTER JOIN departments d
    ON (e.department_id = d.department_id);
*/

--右外链接
/*
SELECT e.last_name, e.department_id, d.department_name
  FROM employees e
 RIGHT OUTER JOIN departments d
    ON (e.department_id = d.department_id);
*/

--全外链接
/*
    SELECT e.last_name, e.department_id, d.department_name
  FROM employees e
  FULL OUTER JOIN departments d
    ON (e.department_id = d.department_id)
*/

--多个分组查询条件
/*
select dep.department_id as "部门ID",dep.department_name as "部门名称",avg(emp.salary) as "平均工资"
from departments dep join employees emp on dep.department_id=emp.department_id
group by dep.department_id,dep.department_name
order by "部门ID" asc
*/

/*
  SELECT dep.department_id, AVG(emp.salary)
          FROM departments dep
          JOIN employees emp
            ON dep.department_id = emp.department_id
         GROUP BY dep.department_id
         ORDER BY AVG(emp.salary) DESC
         FETCH FIRST 1 ROWS ONLY
        
        SELECT *
          FROM EMPLOYEES
         WHERE SALARY = (SELECT MIN(SALARY) FROM EMPLOYEES)
*/

--插入数据
-- INSERT INTO departments(department_id, department_name, 
-- manager_id, location_id)VALUES     
-- (70, 'Public Relations', 100, 1700)

--插入空值
-- insert into departments(department_id, department_name)
-- VALUES(30,'Purchasing')

-- INSERT INTO departments VALUES (100, 'Finance', NULL, NULL);

--插入指定的值
/*INSERT INTO employees
 (employee_id,
   first_name,
   last_name,
   email,
   phone_number,
   hire_date,
   job_id,
   salary,
   commission_pct,
   manager_id,
   department_id)
VALUES
 (219,
   'Louis',
   'Popp',
   'LPOPP2',
   '515.124.4567',
   SYSDATE,
   'AC_ACCOUNT',
   6900,
   NULL,
   205,
   110);
   */

--插入新员工
/*INSERT INTO employees
VALUES
  (114,
   'Den',
   'Raphealy',
   'DRAPHEAL',
   '515.127.4561',
   TO_DATE('FEB 3, 1999', 'MON DD, YYYY'),
   'AC_ACCOUNT',
   11000,
   NULL,
   205,
   110);
*/

--复制表结构
/*create table copy_emp   
(
  employee_id    NUMBER(6) not null,
  first_name     VARCHAR2(20),
  last_name      VARCHAR2(25),
  email          VARCHAR2(25),
  phone_number   VARCHAR2(20),
  hire_date      DATE,
  job_id         VARCHAR2(10),
  salary         NUMBER(8,2),
  commission_pct NUMBER(2,2),
  manager_id     NUMBER(6),
  department_id  NUMBER(4)
)*/

--insert 加子查询

/*
INSERT INTO copy_emp
SELECT * FROM employees WHERE job_id LIKE '%REP%';
*/

--更新数据

/*UPDATE employees SET department_id = 70 WHERE employee_id = 113*/

--在UPDATE语句中使用子查询
/*UPDATE employees
   SET job_id =
       (SELECT job_id FROM employees WHERE employee_id = 205),
       salary =
       (SELECT salary FROM employees WHERE employee_id = 205)
 WHERE employee_id = 114
*/

--在UPDATE 中使用子查询，使更新基于另一个表中的数据
/*UPDATE copy_emp
   SET department_id =
       (SELECT department_id FROM employees WHERE employee_id = 100)
 WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 200)
*/

--用WHERE子句删除指定的记录。
/*DELETE FROM departments WHERE department_name = 'Finance';*/

--在DELETE 中使用子查询
--在DELETE 中使用子查询，使删除基于另一个表中的数据。

/*DELETE FROM employees
WHERE department_id =
      (SELECT department_id
         FROM departments
        WHERE department_name LIKE '%Public%');*/

--在插入操作中使用默认值:
/*INSERT INTO departments
  (department_id, department_name, manager_id)
VALUES
  (300, 'Engineering', DEFAULT);*/

--在更新操作中使用默认值
/*UPDATE departments 
SET manager_id = DEFAULT WHERE department_id = 10*/

/*MERGE INTO copy_emp c
USING employees e
ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN
  UPDATE
     SET c.first_name = e.first_name,
         c.last_name  = e.last_name,
         c.department_id = e.department_id
WHEN NOT MATCHED THEN
  INSERT
  VALUES
    (e.employee_id,
     e.first_name,
     e.last_name,
     e.email,
     e.phone_number,
     e.hire_date,
     e.job_id,
     e.salary,
     e.commission_pct,
     e.manager_id,
     e.department_id);
*/

--创建表
/*
CREATE TABLE dept
 (deptno NUMBER(2),
 dname VARCHAR2(14),
 loc 
VARCHAR2(13));*/
--非空约束
/* 
CREATE TABLE employees(
       employee_id    NUMBER(6),
       last_name      VARCHAR2(25) NOT NULL,
       salary         NUMBER(8,2),
       commission_pct NUMBER(2,2)
       )*/

--唯一约束
-- 可以定义在表级或列级: 
/*   CREATE TABLE employees(
         employee_id    NUMBER(6),
         last_name      VARCHAR2(25) NOT NULL,
         email          VARCHAR2(25) , 
         salary         NUMBER(8,2),  
         commission_pct NUMBER(2,2),  
         hire_date      DATE NOT NULL, 
         CONSTRAINT emp_email_uk UNIQUE(email)
);*/

--PRIMARY KEY 约束

/* CREATE TABLE departments(
department_id             NUMBER(4),        
department_name           VARCHAR2(30) CONSTRAINT dept_name_nn NOT NULL,
manager_id                NUMBER(6),
location_id               NUMBER(4),
CONSTRAINT dept_id_pk PRIMARY KEY(department_id)
) */

--FOREIGN KEY 约束
/* CREATE TABLE employees(
employee_id      NUMBER(6),
last_name        VARCHAR2(25) NOT NULL,
email            VARCHAR2(25),
salary           NUMBER(8,2),
commission_pct   NUMBER(2,2),
hire_date        DATE NOT NULL,
department_id    NUMBER(4),
CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
REFERENCES departments(department_id),
CONSTRAINT emp_email_uk UNIQUE(email)
);*/

/*
  FOREIGN KEY: 在表级指定子表中的列
• REFERENCES: 表示在父表中的列
• ON DELETE CASCADE: 当父表中的列被删除时，子表
  中相对应的列也被删除
• ON DELETE SET NULL: 子表中相应的列置
*/

--添加约束的语法
/*ALTER TABLE table
ADD [CONSTRAINT constraint] type (column)*/

--添加级联约束
/* ALTER TABLE test1 
DROP (pk, fk, col1) CASCADE CONSTRAINTS;*/

--创建视图

/*CREATE VIEW empvu80
AS SELECT  employee_id, last_name, salary
FROM   
employees
WHERE  
department_id = 80
 */

--创建视图时在子查询中给列定义别名
/*CREATE VIEW salvu50
 AS SELECT  employee_id ID_NUMBER, last_name NAME,
 salary*12 ANN_SALARY
 FROM   
 employees
 WHERE  
 department_id = 50;
*/

--创建视图 
/*SELECT *
 FROM salvu50;
*/

--使用CREATE OR REPLACE VIEW 子句修改视图

/*CREATE OR REPLACE VIEW empvu80
 (id_number, name, sal, department_id)
 AS SELECT  employee_id, first_name || ' ' || last_name, 
salary, department_id
 FROM    
employees
 WHERE   department_id = 80*/

--创建复杂视图
/* CREATE VIEWdept_sum_vu
 (name, minsal, maxsal, avgsal)
 AS SELECT d.department_name, MIN(e.salary), 
MAX(e.salary),AVG(e.salary)
 FROM      employees e, departments d
 WHERE     e.department_id = d.department_id 
GROUP BY  d.department_name
*/

--分页
/*SELECT *
  FROM employees
 ORDER BY salary desc -- 分页必须指定排序，保证结果一致性
          OFFSET 0 ROWS -- 跳过前10行（起始行从0开始）当前页码计算公式（理想页码-1）*每页条数
 FETCH NEXT 5 ROWS ONLY; -- 获取接下来的5行（每页5条记录）每页条数
*/

/*视图中使用DML的规定
• 可以在简单视图中执行DML 操作
• 当视图定义中包含以下元素之一时不能删除行数据：– 组函数– GROUP BY子句– DISTINCT关键字– ROWNUM伪列*/

/*WITH CHECK OPTION 子句
• 使用WITH CHECK OPTION 子句确保DML只能在特定
的范围内执*/

/*屏蔽DML 操作
• 可以使用WITH READ ONLY选项屏蔽对视图的DML
操作
• 任何DML 操作都会返回一个Oracle server 错误*/

/*DROP VIEW view;*/

--Top-n分析

/*SELECT ROWNUM as RANK, last_name, salary
 FROM (SELECT last_name, salary FROM employees ORDER BY salary DESC)
WHERE ROWNUM <= 3*/

/* 什么是序列?
序列:
 • 自动提供唯一的数值
• 共享对象
• 主要用于提供主键值
• 代替应用代码
• 将序列值装入内存可以提高访问效率
*/

/*CREATE SEQUENCE 序列名
[INCREMENT BY n]           -- 步长（每次增长的值）
[START WITH n]             -- 起始值
[{MAXVALUE n | NOMAXVALUE}] -- 最大值限制
[{MINVALUE n | NOMINVALUE}] -- 最小值限制
[{CYCLE | NOCYCLE}]         -- 是否循环
[{CACHE n | NOCACHE}];      -- 是否缓存序列值*/

--创建序列

/*  CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 10
START WITH 120
MAXVALUE 9999
NOCACHE
NOCYCLE*/

/* NEXTVAL 和 CURRVAL伪列
• NEXTVAL 返回序列中下一个有效的值，任何用户都可
以引用
• CURRVAL 中存放序列的当前值
• NEXTVAL 应在CURRVAL 之前指定，二者应同时有效*/

--序列应用举例
/*INSERT INTO departments
  (department_id, department_name, location_id)
VALUES INSERT INTO departments
  (department_id, department_name, location_id)
VALUES
  (dept_deptid_seq.NEXTVAL, 'Support', 2500)
*/

/*使用序列
• 将序列值装入内存可提高访问效率
• 序列在下列情况下出现裂缝:– 回滚– 系统异常– 多个表同时使用同一序列
• 如果不将序列的值装入内存(NOCACHE), 可使用表
USER_SEQUENCES 查看序列中下一个有效值*/

/*修改序列
修改序列的增量, 最大值, 最小值, 循环选项, 或是否装入内
存
*/ /*ALTER SEQUENCE dept_deptid_seq
 INCREMENT BY 20
 MAXVALUE 999999
 NOCACHE
 NOCYCLE*/

/* 
 修改序列的注意事项
• 必须是序列的拥有者或对序列有ALTER权限
• 只有后续的序列值会被改变
• 改变序列的初始值只能通过删除序列之后重建序列的方
法实现
• 执行一些校验*/

/*
索引:
 • 一种数据库对象
• 通过指针加速Oracle 服务器的查询速度
• 通过快速定位数据的方法，减少磁盘I/O
 • 索引与表相互独立
• Oracle 服务器自动使用和维护索引*/

/*创建索引
• 自动创建: 在定义PRIMARY KEY或UNIQUE约束后系
统自动在相应的列上创建唯一性索引
• 手动创建: 用户可以在其它列上创建非唯一的索引，以
加速查询*/

/*
SET 集合操作符

UNION/UNION ALL

INTERSECT

MINUS
*/

-- UNION 操作符

-- UNION 操作符返回两个查询的结果集的不重复部分的并集

/*
SELECT employee_id, job_id
  FROM employees
UNION
SELECT employee_id, job_id
  FROM job_history;
*/

-- UNION ALL 操作符
/*
UNION ALL 操作符返回两个查询的结果集的并集以及两个结果
集的重复部分（不去重）
*/

/*SELECT employee_id, job_id, department_id
  FROM employees
UNION ALL
SELECT employee_id, job_id, department_id
  FROM job_history
 ORDER BY employee_id;*/
 
-- INTERSECT 操作符
--INTERSECT 操作符返回两个结果集的交集

/*
SELECT employee_id, job_id
  FROM employees
INTERSECT
SELECT employee_id, job_id
  FROM job_history
*/

--MINUS 操作符
-- MINUS 操作符返回两个结果集的补集

/*SELECT employee_id, job_id
  FROM employees
MINUS
SELECT employee_id, job_id
  FROM job_history
*/


/*使用 SET 集合操作符注意事项
• 在SELECT列表中的列和表达式在数量和数据类型上要相对应
• 括号可以改变执行的顺序
• ORDER BY 子句:– 只能在语句的最后出现– 可以使用第一个查询中的列名, 别名或相对位置

SET 集合操作符
• 除UNION ALL之外，系统会自动将重复的记录删除
• 系统将第一个查询的列名显示在输出中
• 除UNION ALL之外，系统自动按照第一个查询中的第
一个列的升序排列*/

/*SELECT department_id, TO_NUMBER(null) location, hire_date
  FROM employees
UNION
SELECT department_id, location_id, TO_DATE(null)
  FROM departments;
*/






/*SELECT employee_id, job_id,salary
 FROM 
  employees
 UNION
 SELECT employee_id, job_id,0
 FROM 
  job_history;*/
  


--子查询应用举例
/*SELECT last_name
 FROM 
  employees
 WHERE  salary > 
(SELECT salary
 FROM   employees
 WHERE  employee_id = 149) */
 
--成对比较举例
/* 
 SELECT employee_id, manager_id, department_id
   FROM employees
  WHERE (manager_id, department_id) IN
        (SELECT manager_id, department_id
           FROM employees
          WHERE employee_id IN (178, 174))
    AND employee_id NOT IN (178, 174)
*/


--不成对比较举例
/*SELECT employee_id, manager_id, department_id
  FROM employees
 WHERE manager_id IN
       (SELECT manager_id FROM employees WHERE employee_id IN (174, 141))
   AND department_id IN
       (SELECT department_id FROM employees WHERE employee_id IN (174, 141))
   AND employee_id NOT IN (174, 141);
*/


--在FROM子句中使用子查询
/*SELECT a.last_name, a.salary, a.department_id, b.salavg
  FROM employees a,
       (SELECT department_id, AVG(salary) salavg
          FROM employees
         GROUP BY department_id) b
 WHERE a.department_id = b.department_id
   AND a.salary > b.salavg;
*/

--单列子查询应用举例

/*SELECT employee_id,
       last_name,
       (CASE
         WHEN department_id = THEN
          'Canada'
         ELSE
          'USA'
       END) location
  FROM employees;
(
  SELECT department_id FROM departments WHERE location_id = 1800)
*/

--相关子查询
/*
SELECT last_name, salary, department_id
  FROM employees
 outer WHERE salary > (SELECT AVG(salary)
                         FROM employees
                        WHERE department_id = outer.department_id);
                        
                        
                        
*/

-- NOT EXISTS 操作符应用举例
/*SELECT department_id, department_name
  FROM departments d
 WHERE NOT EXISTS
 (SELECT 'X' FROM employees WHERE department_id = d.department_id)
*/

--With子句
/*
WITH dept_costs AS
 (SELECT d.department_name, SUM(e.salary) AS dept_total
    FROM employees e, departments d
   WHERE e.department_id = d.department_id
   GROUP BY d.department_name),
avg_cost AS
 (SELECT SUM(dept_total) / COUNT(*) AS dept_avg FROM dept_costs)
SELECT *
  FROM dept_costs
 WHERE dept_total > (SELECT dept_avg FROM avg_cost)
 ORDER BY department_name;
*/
--列出员工表中每个部门的员工数和部门编号


select department_id,count(1) "员工人数"
from employees
group by department_id

--列出员工表中，员工人数大于3的部门编号和员工人数
select department_id,count(*) as "员工人数"
from employees
group by department_id having count(*) >3

--列出员工表中，员工人数大于3的部门的部门编号，部门名称和部门位置
select emp.department_id,count(*) "员工人数",dep.department_name,lc.street_address
from employees emp join departments dep on emp.department_id = dep.department_id join locations lc on dep.location_id = lc.location_id
group by emp.department_id,dep.department_name,lc.street_address having count(*) > 3  


--查询员工数大于3的部门名称和员工数
select dep.department_name,x.confirm
from departments dep,(select department_id,count(*) confirm
from employees
group by department_id having count(*) >3) x
where dep.department_id = x.department_id

--找出工资比Gietz高的员工
select last_name,salary
from employees
where salary >(
      select salary
      from employees
      where last_name = 'Gietz'
)

--列出所有员工的姓名和其上级的姓名（表的自连接）
select e.last_name,mg.last_name
from employees e , employees mg
where mg.employee_id = e.manager_id


select *
from(
       select departments.department_id,departments.department_name,avg(salary)
       from employees join departments on employees.department_id = departments.department_id
       group by departments.department_id,departments.department_name
       order by avg(salary) desc
)
where rownum <= 2


--查找出不在部门30，且比部门30中任何一个人工资都高的员工的姓名、部门名称

select last_name,department_name
from employees join departments on employees.department_id = departments.department_id
where departments.department_id != 30 and salary >(
      select max(salary)
      from employees
      where department_id = 30
                                          
)











